﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using NPOI.SS.UserModel;
using System.Data;
using NPOI.XSSF.UserModel;
using NPOI.HSSF.UserModel;
using System.IO;
using NPOI.SS.Formula.Eval;
using System.Collections;
using System.Windows.Forms;

namespace BellAssistant.Common.CommonHelper
{
    public class ExcelNPOIHelper
    {
        #region 方式一
        /// <summary>
        /// DataTable数据导出到Excel指定的工作簿
        /// </summary>
        /// <param name="dt">DataTable</param>
        /// <param name="excelFileName">Excel文件名</param>
        /// <param name="excelSheetName">Excel文件工作簿名</param>
        public static void DataTableToExcel(DataTable dt, string excelFileName, string excelSheetName = "Sheet1")
        {
            IWorkbook workbook = null;
            ISheet sheet = null;
            string ext = Path.GetExtension(excelFileName).ToLower();
            try
            {
                if (ext == ".xls")
                    workbook = new HSSFWorkbook();
                else
                    workbook = new XSSFWorkbook();

                sheet = workbook.CreateSheet(excelSheetName);

                ICellStyle HeadercellStyle = workbook.CreateCellStyle();
                HeadercellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
                HeadercellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
                HeadercellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
                HeadercellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
                HeadercellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
                //字体
                IFont headerfont = workbook.CreateFont();
                headerfont.Boldweight = (short)FontBoldWeight.Bold;
                HeadercellStyle.SetFont(headerfont);

                //用column name 作为列名
                int icolIndex = 0;
                IRow headerRow = sheet.CreateRow(0);
                foreach (DataColumn item in dt.Columns)
                {
                    ICell cell = headerRow.CreateCell(icolIndex);
                    cell.SetCellValue(item.ColumnName);
                    cell.CellStyle = HeadercellStyle;
                    icolIndex++;
                }

                ICellStyle cellStyle = workbook.CreateCellStyle();

                //为避免日期格式被Excel自动替换，所以设定 format 为 『@』 表示一率当成text來看
                cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("@");
                cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
                cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
                cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
                cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;

                IFont cellfont = workbook.CreateFont();
                cellfont.Boldweight = (short)FontBoldWeight.Normal;
                cellStyle.SetFont(cellfont);

                //建立内容行
                int iRowIndex = 1;
                int iCellIndex = 0;
                foreach (DataRow Rowitem in dt.Rows)
                {
                    IRow DataRow = sheet.CreateRow(iRowIndex);
                    foreach (DataColumn Colitem in dt.Columns)
                    {
                        ICell cell = DataRow.CreateCell(iCellIndex);
                        cell.SetCellValue(Rowitem[Colitem].ToString());
                        cell.CellStyle = cellStyle;
                        iCellIndex++;
                    }
                    iCellIndex = 0;
                    iRowIndex++;
                }

                //自适应列宽度
                for (int i = 0; i < icolIndex; i++)
                {
                    sheet.AutoSizeColumn(i);
                }

                //写Excel
                FileStream file = new FileStream(excelFileName, FileMode.OpenOrCreate);
                workbook.Write(file);
                //file.Flush();  --file.Close()后会自动将缓存中数据写入。此处加了代码会报错“无法打开已关闭的文件”
                file.Close();

                //MessageBox.Show("导出Excel成功！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            catch (Exception ex)
            {
                MessageBox.Show("导出Excel失败！\n" + ex.Message, "提示", MessageBoxButtons.OK, MessageBoxIcon.None);
            }
            finally
            {
                sheet = null;
                workbook = null;
                GC.Collect();
            }
        }

        /// <summary>
        /// Excel数据读到Datatable
        /// </summary>
        /// <param name="filePath">Excel文件目录</param>
        /// <param name="tableName">Datatable表名</param>
        /// <param name="iSheetIndex">Excel sheet index</param>
        /// <returns></returns>
        public static DataTable ExcelToDataTable(string filePath, string tableName, int iSheetIndex = 0)
        {
            string ext = Path.GetExtension(filePath).ToLower();

            DataTable dt = new DataTable();
            if (!string.IsNullOrEmpty(tableName))
            {
                dt.TableName = tableName;
            }

            IWorkbook workbook = null;
            ISheet sheet = null;

            try
            {
                using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
                {
                    if (ext.Equals(".xls"))
                        workbook = new HSSFWorkbook(file);
                    else
                        workbook = new XSSFWorkbook(file);
                    sheet = workbook.GetSheetAt(iSheetIndex);

                    //列头
                    foreach (ICell item in sheet.GetRow(sheet.FirstRowNum).Cells)
                    {
                        dt.Columns.Add(item.ToString(), typeof(string));
                    }

                    //写入内容
                    IEnumerator rows = sheet.GetRowEnumerator();
                    while (rows.MoveNext())
                    {
                        IRow row = (IRow)rows.Current;
                        if (row.RowNum == sheet.FirstRowNum)
                        {
                            continue;
                        }

                        DataRow dr = dt.NewRow();
                        foreach (ICell item in row.Cells)
                        {
                            switch (item.CellType)
                            {
                                case CellType.Boolean:
                                    dr[item.ColumnIndex] = item.BooleanCellValue;
                                    break;
                                case CellType.Error:
                                    dr[item.ColumnIndex] = ErrorEval.GetText(item.ErrorCellValue);
                                    break;
                                case CellType.Formula:
                                    switch (item.CachedFormulaResultType)
                                    {
                                        case CellType.Boolean:
                                            dr[item.ColumnIndex] = item.BooleanCellValue;
                                            break;
                                        case CellType.Error:
                                            dr[item.ColumnIndex] = ErrorEval.GetText(item.ErrorCellValue);
                                            break;
                                        case CellType.Numeric:
                                            if (DateUtil.IsCellDateFormatted(item))
                                            {
                                                dr[item.ColumnIndex] = item.DateCellValue.ToString("yyyy-MM-dd hh:MM:ss");
                                            }
                                            else
                                            {
                                                dr[item.ColumnIndex] = item.NumericCellValue;
                                            }
                                            break;
                                        case CellType.String:
                                            string str = item.StringCellValue;
                                            if (!string.IsNullOrEmpty(str))
                                            {
                                                dr[item.ColumnIndex] = str.ToString();
                                            }
                                            else
                                            {
                                                dr[item.ColumnIndex] = null;
                                            }
                                            break;
                                        case CellType.Unknown:
                                        case CellType.Blank:
                                        default:
                                            dr[item.ColumnIndex] = string.Empty;
                                            break;
                                    }
                                    break;
                                case CellType.Numeric:
                                    if (DateUtil.IsCellDateFormatted(item))
                                    {
                                        dr[item.ColumnIndex] = item.DateCellValue.ToString("yyyy-MM-dd HH:mm:ss");
                                    }
                                    else
                                    {
                                        dr[item.ColumnIndex] = item.NumericCellValue;
                                    }
                                    break;
                                case CellType.String:
                                    string strValue = item.StringCellValue;
                                    if (string.IsNullOrEmpty(strValue))
                                    {
                                        dr[item.ColumnIndex] = strValue.ToString();
                                    }
                                    else
                                    {
                                        dr[item.ColumnIndex] = null;
                                    }
                                    break;
                                case CellType.Unknown:
                                case CellType.Blank:
                                default:
                                    dr[item.ColumnIndex] = string.Empty;
                                    break;
                            }
                        }
                        dt.Rows.Add(dr);
                    }
                }
            }
            catch (Exception) { }
            finally
            {
                sheet = null;
                workbook = null;
                GC.Collect();
            }
            return dt;
        }
        #endregion

        #region 方式二

        #region 读取excel中内容返回DataTable
        public static DataTable ExcelToDataTable(string excelPath, string sheetName)
        {
            return ExcelToDataTable(excelPath, sheetName, true);
        }
        public static DataTable ExcelToDataTable(string excelPath, string sheetName, bool firstRowAsHeader)
        {
            string ext = Path.GetExtension(excelPath).ToLower();
            IWorkbook workbook = null;
            IFormulaEvaluator evaluator = null;
            ISheet sheet = null;

            using (FileStream fileStream = new FileStream(excelPath, FileMode.Open, FileAccess.Read))
            {
                if (ext == ".xls") //97-2003
                {
                    workbook = new HSSFWorkbook(fileStream);
                    evaluator = new HSSFFormulaEvaluator(workbook);
                    sheet = workbook.GetSheet(sheetName) as HSSFSheet;
                    if (sheet == null) //找不到SheetName的工作簿，默认使用第一个工作簿
                        sheet = workbook.GetSheetAt(0) as HSSFSheet;
                }
                else //2007+
                {
                    workbook = new XSSFWorkbook(fileStream);
                    evaluator = new XSSFFormulaEvaluator(workbook);
                    sheet = workbook.GetSheet(sheetName) as XSSFSheet;
                    if (sheet == null) //找不到SheetName的工作簿，默认使用第一个工作簿
                        sheet = workbook.GetSheetAt(0) as XSSFSheet;
                }
                return ExcelToDataTable(sheet, evaluator, firstRowAsHeader);
            }
        }
        private static DataTable ExcelToDataTable(ISheet sheet, IFormulaEvaluator evaluator, bool firstRowAsHeader)
        {
            if (firstRowAsHeader)
            {
                return ExcelToDataTableFirstRowAsHeader(sheet, evaluator);
            }
            else
            {
                return ExcelToDataTable(sheet, evaluator);
            }
        }
        private static DataTable ExcelToDataTableFirstRowAsHeader(ISheet sheet, IFormulaEvaluator evaluator)
        {
            using (DataTable dt = new DataTable())
            {
                IRow firstRow = sheet.GetRow(0);
                int cellCount = GetCellCount(sheet);

                for (int i = 0; i < cellCount; i++)
                {
                    if (firstRow.GetCell(i) != null)
                    {
                        dt.Columns.Add(firstRow.GetCell(i).StringCellValue ?? string.Format("F{0}", i + 1), typeof(string));
                    }
                    else
                    {
                        dt.Columns.Add(string.Format("F{0}", i + 1), typeof(string));
                    }
                }

                for (int i = 1; i <= sheet.LastRowNum; i++)
                {
                    IRow row = sheet.GetRow(i);
                    DataRow dr = dt.NewRow();
                    FillDataRowByHSSFRow(row, evaluator, ref dr);
                    dt.Rows.Add(dr);
                }

                dt.TableName = sheet.SheetName;
                return dt;
            }
        }
        private static DataTable ExcelToDataTable(ISheet sheet, IFormulaEvaluator evaluator)
        {
            using (DataTable dt = new DataTable())
            {
                if (sheet.LastRowNum != 0)
                {
                    int cellCount = GetCellCount(sheet);

                    for (int i = 0; i < cellCount; i++)
                    {
                        dt.Columns.Add(string.Format("F{0}", i), typeof(string));
                    }

                    for (int i = 0; i < sheet.FirstRowNum; ++i)
                    {
                        DataRow dr = dt.NewRow();
                        dt.Rows.Add(dr);
                    }

                    for (int i = sheet.FirstRowNum; i <= sheet.LastRowNum; i++)
                    {
                        IRow row = sheet.GetRow(i);
                        DataRow dr = dt.NewRow();
                        FillDataRowByHSSFRow(row, evaluator, ref dr);
                        dt.Rows.Add(dr);
                    }
                }

                dt.TableName = sheet.SheetName;
                return dt;
            }
        }
        private static void FillDataRowByHSSFRow(IRow row, IFormulaEvaluator evaluator, ref DataRow dr)
        {
            if (row != null)
            {
                for (int j = 0; j < dr.Table.Columns.Count; j++)
                {
                    ICell cell = row.GetCell(j);

                    if (cell != null)
                    {
                        switch (cell.CellType)
                        {
                            case CellType.Blank:
                                dr[j] = DBNull.Value;
                                break;
                            case CellType.Boolean:
                                dr[j] = cell.BooleanCellValue;
                                break;
                            case CellType.Numeric:
                                if (DateUtil.IsCellDateFormatted(cell))
                                {
                                    dr[j] = cell.DateCellValue;
                                }
                                else
                                {
                                    dr[j] = cell.NumericCellValue;
                                }
                                break;
                            case CellType.String:
                                dr[j] = cell.StringCellValue;
                                break;
                            case CellType.Error:
                                dr[j] = cell.ErrorCellValue;
                                break;
                            case CellType.Formula:
                                cell = evaluator.EvaluateInCell(cell) as HSSFCell;
                                dr[j] = cell.ToString();
                                break;
                            default:
                                throw new NotSupportedException(string.Format("Catched unhandle CellType[{0}]", cell.CellType));
                        }
                    }
                }
            }
        }
        private static int GetCellCount(ISheet sheet)
        {
            int firstRowNum = sheet.FirstRowNum;
            int cellCount = 0;
            for (int i = sheet.FirstRowNum; i <= sheet.LastRowNum; ++i)
            {
                IRow row = sheet.GetRow(i);

                if (row != null && row.LastCellNum > cellCount)
                {
                    cellCount = row.LastCellNum;
                }
            }
            return cellCount;
        }
        #endregion

        #region 读取excel中内容,选择固定的行作为列名 返回DataTable
        /// <summary>
        /// 读取excel中内容,选择固定的行作为列名并开始读取数据到DataTable中
        /// </summary>
        /// <param name="excelPath">url</param>
        /// <param name="sheetName">sheet名</param>
        /// <param name="rowNumber">从哪行开始读取数据并作为表头</param>
        /// <returns>DataTable</returns>
        public static DataTable ExcelToDataTableFromSpecifiedRowNumber(string excelPath, string sheetName, int rowNumber)
        {
            try
            {
                if (rowNumber > 0)
                {
                    return ExcelToDataTable(excelPath, sheetName, false, rowNumber);
                }
                else
                {
                    return ExcelToDataTable(excelPath, sheetName, true, rowNumber);
                }
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog.Error(ex.Message);
                if (ex.Message.Contains("Invalid header signature"))
                {
                    throw new Exception("Excel格式不能被识别，请选择.xls或.xlsx文件，或者将文件另存为可读取的.xls或.xlsx文件。");
                }
                else if (ex.Message.ToLower().Contains("used") || ex.Message.Contains("正由另一进程使用，因此该进程无法访问此文件。"))
                {
                    throw new Exception("该文件正在使用，因此该程序无法访问此文件，请关闭文件后再次导入。");
                }
                else
                {
                    throw ex;
                }
            }
        }
        public static DataTable ExcelToDataTable(string excelPath, string sheetName, bool firstRowAsHeader, int rowNumber)
        {
            string ext = Path.GetExtension(excelPath).ToLower();

            using (FileStream fileStream = new FileStream(excelPath, FileMode.Open, FileAccess.Read))
            {
                IWorkbook workbook = null;
                IFormulaEvaluator evaluator = null;
                ISheet sheet = null;

                if (ext == ".xls") //97-2003
                {
                    workbook = new HSSFWorkbook(fileStream);
                    evaluator = new HSSFFormulaEvaluator(workbook);
                    sheet = workbook.GetSheet(sheetName) as HSSFSheet;
                    if (sheet == null) //找不到SheetName的工作簿，默认使用第一个工作簿
                        sheet = workbook.GetSheetAt(0) as HSSFSheet;
                }
                else //2007+
                {
                    workbook = new XSSFWorkbook(fileStream);
                    evaluator = new XSSFFormulaEvaluator(workbook);
                    if (string.IsNullOrEmpty(sheetName))
                        sheet = workbook.GetSheetAt(0) as XSSFSheet;
                    else
                        sheet = workbook.GetSheet(sheetName) as XSSFSheet;
                }
                return ExcelToDataTable(sheet, evaluator, firstRowAsHeader, rowNumber);
            }
        }
        private static DataTable ExcelToDataTable(ISheet sheet, IFormulaEvaluator evaluator, bool firstRowAsHeader, int rowNumber)
        {
            if (firstRowAsHeader)
            {
                return ExcelToDataTableFirstRowAsHeader(sheet, evaluator);
            }
            else
            {
                if (rowNumber == 0)
                {
                    return ExcelToDataTable(sheet, evaluator);
                }
                else
                {
                    return ExcelToDataTableRowNumberRowAsHeader(sheet, evaluator, rowNumber);
                }
            }
        }
        private static DataTable ExcelToDataTableRowNumberRowAsHeader(ISheet sheet, IFormulaEvaluator evaluator, int rowNumber)
        {
            using (DataTable dt = new DataTable())
            {
                IRow firstRow = sheet.GetRow(rowNumber - 1);
                int cellCount = GetCellCount(sheet);

                for (int i = 0; i < cellCount; i++)
                {
                    if (firstRow.GetCell(i) != null)
                    {
                        //个别情况需要进行下面的设置，避免有的列为整型的取值会报异常，
                        firstRow.GetCell(i).SetCellType(CellType.String);
                        dt.Columns.Add(firstRow.GetCell(i).StringCellValue ?? string.Format("F{0}", i + 1), typeof(string));
                    }
                    else
                    {
                        dt.Columns.Add(string.Format("F{0}", i + 1), typeof(string));
                    }
                }

                for (int i = rowNumber; i <= sheet.LastRowNum; i++)
                {
                    IRow row = sheet.GetRow(i);
                    DataRow dr = dt.NewRow();
                    FillDataRowByHSSFRow(row, evaluator, ref dr);
                    dt.Rows.Add(dr);
                }

                dt.TableName = sheet.SheetName;
                return dt;
            }
        }
        #endregion

        #endregion
    }
}
